/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.hualu.zks.Dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.hualu.zks.Model.Node;
import static com.hualu.zks.Dao.ConnDb.conn;

/**
 *
 * @author Administrator
 */
public class NodeDao {

	private static NodeDao nodeDao;
	private static String table_name = "TB_GRADE";
	private static String nodeid = "GRADE_ID";
	private static String nodename = "GRADE_NAME";
	private static String nodeip = "NODE_IP";
    
	
	
	public static NodeDao getNodeDao() {
		if (nodeDao == null) {
			nodeDao = new NodeDao();
		}
		return nodeDao;
	}
	
    /**
     * 获取节点列表
     * @return
     */
    public ArrayList<Node> getAllNode() {
        try {
             ArrayList<Node>  nodeList=new ArrayList<Node>();
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return null;
//            }
            Statement statement = conn.createStatement();
            String sql = "SELECT "+nodeid+","+nodename+","+nodeip+" FROM "+table_name+";";
  //          System.out.println("sql_que:"+sql);
            try (ResultSet rs = statement.executeQuery(sql)) {
                Node node;
                while (rs.next()) {
                    node = new Node();
                    node.setNodeId(rs.getInt(nodeid));
                    node.setName(rs.getString(nodename));
                    if(rs.getString(nodeip)==null||"".equals(rs.getString(nodeip))){
                        continue;
                    }
                    node.setIPAdd(rs.getString(nodeip));
                    nodeList.add(node);
                }
                rs.close();
                return nodeList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
	}
    
    
    /**
     * 增加一个节点
     * @param node
     * @return
     */
    public static int addNode(Node node){
    	return addNode(node.getName(),node.getIPAdd());
    }
	
	
    /**
     * 增加一个节点
     * @param nodeName
     * @param nodeIp
     * @return
     */
    public static int addNode(String nodeName,String nodeIp){
        int ret = 0;
        try {     
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return 0;
//            }
            Statement statement = conn.createStatement();
            String sql = "INSERT INTO "+ table_name+" ("+nodename+","+nodeip+") VALUES ('" + nodeName + "','" + nodeIp + "');";
  //         System.out.println("sql_que:"+sql);
            ret = statement.executeUpdate(sql);
        }    
         catch (SQLException e) {
            System.out.println(Arrays.toString(e.getStackTrace()));
        }
        return ret;
    }
    
    
    
    /**
     * 删除节点
     * @param node
     * @return
     */
    public static boolean delNode(Node node) {
    	return delNode(node.getNodeId());
    }
    
    
    /**
     * 删除节点
     * @param node
     * @return
     */
    	public static boolean delNode(int nodeId) {
        try {
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return false;
//            }
            Statement statement = conn.createStatement();
            String sql = "DELETE FROM "+table_name+" WHERE "+nodeid +"='"+nodeId+"';";
 //           System.out.println("sql_que:" + sql);
            statement.executeUpdate(sql);
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    

    	
    public static boolean editNode(Node node) {
        try {
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return false;
//            }
            Statement statement = conn.createStatement();
            String sql = "UPDATE "+table_name+" SET "+nodename+"='"+node.getName()+"',"+ nodeip +"='"+node.getIPAdd()+ "' WHERE "+nodeid+"=" + node.getNodeId() + ";";
              System.out.println("sql_que:" + sql);
            statement.executeUpdate(sql);
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    
//    public static String getIPByName(String strName){
//        try {
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return null;
//            }
//            String ipAddr;
//            Statement statement = conn.createStatement();
//            String sql = "SELECT INSTINCT "+nodeip+" FROM "+table_name+" WHERE "+nodename+"='" + strName + "';";
////              System.out.println("sql_que:" + sql);
//            try (ResultSet rs = statement.executeQuery(sql)) {
//                ipAddr=rs.getString(nodename);
//                rs.close();
//                return ipAddr;
//            }
//        } catch (SQLException ex) {
//            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
//        }     return null;
//    }
    
    
    
    public static String getIPById(int nodeId){
        try {
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return null;
//            }
            String ipAddr;
            Statement statement = conn.createStatement();
//            String sql = "SELECT DISTINCT "+nodeip+" FROM "+table_name+" WHERE "+nodeid+"='" + nodeId + "';";
            String sql = "SELECT DISTINCT NODE_IP FROM TB_GRADE WHERE GRADE_ID='" + nodeId + "';";
//              System.out.println("sql_que:" + sql);
            try (ResultSet rs = statement.executeQuery(sql)) {
                 while(rs.next()){
                ipAddr=rs.getString("NODE_IP");
                rs.close();
                return ipAddr;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    
    public static String getNodeNameById(int nodeId){
        try {
//            Connection conn;
//            conn = ConnDb.connDb();
//            if (conn == null) {
//                return null;
//            }
            String nodeName="";
            Statement statement = conn.createStatement();
            String sql = "SELECT DISTINCT "+nodename+" FROM "+table_name+" WHERE "+nodeid+"='" + nodeId + "';";
              System.out.println("sql_que:" + sql);
            try (ResultSet rs = statement.executeQuery(sql)) {
                while(rs.next()){
                nodeName=rs.getString(nodename);
                rs.close();
                return nodeName;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    
        public static Boolean isNodeNameValide(String nodeName){
        try {
            int count;
            Statement statement = conn.createStatement();
            String sql = "SELECT count(*) as total"+" FROM "+table_name+" WHERE "+nodename+"='" + nodeName + "';";
            try (ResultSet rs = statement.executeQuery(sql)) {
                while(rs.next()){
                count=Integer.valueOf((rs.getString("total")));
                rs.close();
                return count>0;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
        
            
        public static Boolean isNodeIpValide(String nodeIp){
        try {
            int count;
            Statement statement = conn.createStatement();
            String sql = "SELECT count(*) as total"+" FROM "+table_name+" WHERE "+nodeip+"='" + nodeIp + "';";
            try (ResultSet rs = statement.executeQuery(sql)) {
                while(rs.next()){
                count=Integer.valueOf((rs.getString("total")));
                rs.close();
                return count>0;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
        
}
